--Create the NextGenDataBase database.

CREATE DATABASE NextGenDataBase;
GO

USE NextGenDataBase;
GO

CREATE TABLE member_info
   (member_id        int PRIMARY KEY NOT NULL,
    member_nick      char(16)        NOT NULL,
    member_pass      char(16)		 NOT NULL,
    member_firstname char(16)		 NOT NULL,
    member_lastname  char(16)		 NOT NULL,
    member_email     char(16)		 NOT NULL,
    member_onlineLed tinyint		 NOT NULL,
    member_photo     int			 NOT NULL,
    member_status    text			 NOT NULL
    );
GO

CREATE TABLE address_info
	(address_id      int PRIMARY KEY REFERENCES
	 member_info (member_id)		 NOT NULL,
	 address_country char(16)		 NOT NULL,
	 address_city    char(16)		 NOT NULL,
	 address_street  char(16)		 NOT NULL,
	 address_house   int			 NOT NULL
	);
GO

CREATE TABLE dateOfBirth_info
	(dateOfBirth_id    int PRIMARY KEY REFERENCES 
	 member_info (member_id)	NOT NULL,
	 dateOfBirth_month char(16) NOT NULL,
	 dateOfBirth_day   tinyint  NOT NULL,
	 dateOfBirth_year  int      NOT NULL
	);
GO

CREATE TABLE mobileNumber_info
	(mobileNumber_id       int FOREIGN KEY REFERENCES 
	 member_info (member_id)               NOT NULL,
	 mobileNumber_operator char(16)		   NOT NULL,
	 mobileNumber_number   int PRIMARY KEY NOT NULL,
	 mobileNumber_code     tinyint         NOT NULL
	);
GO

CREATE TABLE homeNumber_info
	(homeNumber_id       int FOREIGN KEY REFERENCES 
	 member_info (member_id)               NOT NULL,	 
	 homeNumber_number   int PRIMARY KEY   NOT NULL,
	 homeNumber_code     tinyint           NOT NULL
	);
GO

CREATE TABLE wall_info
	(wall_id           int FOREIGN KEY REFERENCES 
	 member_info (member_id)               NOT NULL,
	 wall_topic        char(16)            NOT NULL,	
	 wall_txt          text			       NOT NULL,
	 wall_link		   char			       NOT NULL,
	 wall_friendId     int				   NOT NULL,
	 wall_textId	   int PRIMARY KEY	   NOT NULL
	);
GO

CREATE TABLE news_info
	(news_id       int FOREIGN KEY REFERENCES 
	 member_info (member_id)               NOT NULL,	 
	 news_topic        char(16)            NOT NULL,
	 news_txt          text			       NOT NULL,
	 news_textId	   int PRIMARY KEY	   NOT NULL
	);
GO

CREATE TABLE friend_info
	(friend_memberId	   int FOREIGN KEY REFERENCES 
	 member_info (member_id)                  NOT NULL,	 
	 friend_friendId       int                NOT NULL
	);
GO

CREATE TABLE wallComment_info
	(wallComment_wallId      int FOREIGN KEY REFERENCES 
	 wall_info (wall_textId)				    NOT NULL,
	 wallComment_friendId    int NOT NULL,
	 wallComment_id 	     int   PRIMARY KEY  NOT NULL, 
	 wallComment_txt         text			    NOT NULL,
	 wallComment_date        char(16)		    NOT NULL,
	 wallComment_time		 char(16)		    NOT NULL
	);
GO

CREATE TABLE fileInf_info
	(fileInf_memberId	   int FOREIGN KEY REFERENCES 
	 member_info (member_id)                  NOT NULL,	 
	 fileInf_Id            int   PRIMARY KEY  NOT NULL,
	 fileInf_name          char(16)			  NOT NULL,
	 fileInf_description   char(16)			  NOT NULL,
	 fileInf_size          int			      NOT NULL,
	 fileInf_rating        int			      NOT NULL,
	 fileInf_format        char(16)			  NOT NULL,	 
	 fileInf_category      char(16)			  NOT NULL,
	 fileInf_address	   char(16)		      NOT NULL
	);
GO

CREATE TABLE fileInfTag_info
	(fileInfTag_fileInfId	int FOREIGN KEY REFERENCES 
	 fileInf_info (fileInf_id)                NOT NULL,	 	 
	 fileInfTag_id	        int PRIMARY KEY	  NOT NULL,
	 fileInfTag_txt         char(16)          NOT NULL
	);
GO

CREATE TABLE fileMember_info
	(fileMember_memberId	   int     NOT NULL,	 
	 fileMember_friendId       int   FOREIGN KEY REFERENCES
	 fileInf_info (fileInf_id)         NOT NULL
	);
GO

CREATE TABLE film_info
	(film_fileInfId	int PRIMARY KEY REFERENCES 
	 fileInf_info (fileInf_id)                NOT NULL,	 	 
	 film_quality     char(16)					  NOT NULL,
	 film_ratingIMDb  char(16)          NOT NULL,
	 film_time  int          NOT NULL,
	 film_sound  char(16)          NOT NULL,
	 film_issued  char(16)          NOT NULL,
	 film_released  char(16)          NOT NULL,
	 film_directedBy  char(16)          NOT NULL
	);
GO

CREATE TABLE audio_info
	(audio_fileInfId	int PRIMARY KEY REFERENCES 
	 fileInf_info (fileInf_id)                NOT NULL,	 	 
	 audio_direction     char(16)					  NOT NULL,
	 audio_year  int          NOT NULL,
	 audio_time  int          NOT NULL,
	 audio_bitrate  char(16)          NOT NULL
	);
GO

CREATE TABLE book_info
	(book_fileInfId	int PRIMARY KEY REFERENCES 
	 fileInf_info (fileInf_id)                NOT NULL,	 	 
	 book_edition     char(16)					  NOT NULL,
	 book_ISBN  int          NOT NULL,
	 book_amountPage  int          NOT NULL,
	 book_year  int          NOT NULL
	);
GO

CREATE TABLE video_info
	(video_fileInfId	int PRIMARY KEY REFERENCES 
	 fileInf_info (fileInf_id)                NOT NULL,	 	 
	 video_edition     char(16)					  NOT NULL,
	 video_time        int          NOT NULL
	);
GO

CREATE TABLE filmGenre_info
	(filmGenre_filmId	int FOREIGN KEY REFERENCES 
	 film_info (film_fileInfId)                NOT NULL,	 	 
	 filmGenre_genre     char(16)			   NOT NULL
	);
GO

CREATE TABLE filmActor_info
	(filmActor_filmId	int FOREIGN KEY REFERENCES 
	 film_info (film_fileInfId)                NOT NULL,	 	 
	 filmActor_actor     char(16)			   NOT NULL
	);
GO

CREATE TABLE audioArtist_info
	(audioArtist_audioId	int FOREIGN KEY REFERENCES 
	 audio_info (audio_fileInfId)             NOT NULL,	 	 
	 audioArtist_artist     char(16)		NOT NULL
	);
GO

CREATE TABLE bookAutor_info
	(bookAutor_bookId	int FOREIGN KEY REFERENCES 
	 book_info (book_fileInfId)             NOT NULL,	 	 
	 bookAutor_autor     char(16)		NOT NULL
	);
GO

CREATE TABLE videoGenre_info
	(videoGenre_videoId	int FOREIGN KEY REFERENCES 
	 video_info (video_fileInfId)             NOT NULL,	 	 
	 videoGenre_genre     char(16)		NOT NULL
	);
GO